package com.cn.jdia.springmvc.handlers;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

@Controller
public class HistoryController {

	@RequestMapping("/getpechistory")
	public String GetPecHistory(String start, String end, HttpServletResponse response) throws IOException {
		System.out.println("GetPecHistory--");
		System.out.println("start:" + start);
		System.out.println("end:" + end);

		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();

		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource2");

		JSONArray jsonData = new JSONArray();
		int count = 0;

		try {
			Connection conn = ds.getConnection();
			Statement st = conn.createStatement();

			StringBuffer sb = new StringBuffer(
					"select CDATE,CREATTIME,CONTENT,SUBPERSON from ATT_PECCANCY where CDATE>='").append(start)
							.append("' and CDATE<='").append(end).append("' and SUBACCOUNT='admin' ORDER BY CDATE");
			// System.out.println(sb.toString());

			String Date = "";
			String Creat = "";
			String Content = "";
			String SubjectName = "";
			ResultSet rs = st.executeQuery(sb.toString());

			JSONObject jo = null;
			while (rs.next()) {
				jo = new JSONObject();

				Date = rs.getString("CDATE");
				Creat = rs.getString("CREATTIME");
				Content = rs.getString("CONTENT");
				SubjectName = rs.getString("SUBPERSON");

				jo.put("Date", Date);
				jo.put("id", Creat);
				jo.put("Content", Content);
				jo.put("SubjectName", SubjectName);

				jsonData.add(jo);
				count++;
			}

			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		JSONObject jsonObject = new JSONObject();
		jsonObject.put("rows", jsonData);// JSONArray
		jsonObject.put("total", count);// 总记录数

		System.out.println(jsonObject.toString());
		out.write(jsonObject.toString());
		out.flush();
		out.close();

		return null;
	}

	@RequestMapping("/getprohistory")
	public String GetProHistory(String start, String end, HttpServletResponse response) throws IOException {
		System.out.println("GetProHistory--");
		System.out.println("start:" + start);
		System.out.println("end:" + end);

		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();

		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource2");

		JSONArray jsonData = new JSONArray();
		int count = 0;

		try {
			Connection conn = ds.getConnection();
			Statement st = conn.createStatement();

			StringBuffer sb = new StringBuffer(
					"select to_char(MODIFY_DATE,'YYYY-MM-DD') CDATE,FIELD0002,FIELD0003,FIELD0004,FIELD0007,FIELD0006,FIELD0008 from FORMMAIN_6250_AOC WHERE to_char(MODIFY_DATE,'YYYY-MM-DD') >='")
							.append(start).append("' and to_char(MODIFY_DATE,'YYYY-MM-DD')<='").append(end)
							.append("' ORDER BY MODIFY_DATE");
			// System.out.println(sb.toString());

			String Date = "";
			String jiaci = "";
			String lvke = "";
			String huoyou = "";
			String fangxing = "";
			String hangban="";
			String zhixing="";

			ResultSet rs = st.executeQuery(sb.toString());

			DateTimeFormatter format = DateTimeFormat.forPattern("yyyy-MM-dd");

			JSONObject jo = null;
			while (rs.next()) {
				jo = new JSONObject();
				Date = rs.getString("CDATE"); // 应该是前一天的

				// modify by MN
				DateTime datetime = DateTime.parse(Date, format);
				DateTime dt2 = datetime.minusDays(1);
				Date = dt2.toString("yyyy-MM-dd");

				jiaci = rs.getString("FIELD0002");
				lvke = rs.getString("FIELD0003");
				huoyou = rs.getString("FIELD0004");
				fangxing = rs.getString("FIELD0007");
				hangban=rs.getString("FIELD0006");
				zhixing=rs.getString("FIELD0008");

				jo.put("Date", Date);
				jo.put("jiaci", jiaci);
				jo.put("lvke", lvke);
				jo.put("huoyou", huoyou);
				jo.put("fangxing", fangxing);
				jo.put("hangban", hangban);
				jo.put("zhixing", zhixing);

				jsonData.add(jo);
				count++;
			}

			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		JSONObject jsonObject = new JSONObject();
		jsonObject.put("rows", jsonData);// JSONArray
		jsonObject.put("total", count);// 总记录数

		System.out.println(jsonObject.toString());
		out.write(jsonObject.toString());
		out.flush();
		out.close();

		return null;
	}

	@RequestMapping("/gettrahistory")
	public String GetTraHistory(String start, String end, HttpServletResponse response) throws IOException {
		System.out.println("start:" + start);
		System.out.println("end:" + end);

		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();

		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource2");

		JSONArray jsonData = new JSONArray();
		int count = 0;

		String startdate = start + " 00:00:00";
		String enddate = end + " 00:00:00";

		try {
			Connection conn = ds.getConnection();
			Statement st = conn.createStatement();

			StringBuffer sb = new StringBuffer("SELECT 日期,出港旅客,客座率,进港旅客,过站旅客,商用座位,要客保障 ,货物,行李,邮件,特殊事件  FROM YUNSHU ")
					.append("WHERE 日期 >=TO_DATE('").append(startdate)
					.append("', 'yyyy-mm-dd hh24:mi:ss') and 日期 <= TO_DATE('").append(enddate)
					.append("', 'yyyy-mm-dd hh24:mi:ss')");

			String Date = "";
			String chugang = "";
			String kzl = "";
			String jingang = "";
			String guozhan = "";
			String shangyong = "";
			String yaoke = "";
			String huowu = "";
			String xingli = "";
			String youjian = "";
			String teshu = "";

			ResultSet rs = st.executeQuery(sb.toString());
			DateTimeFormatter format = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
			JSONObject jo = null;
			while (rs.next()) {
				jo = new JSONObject();
				Date = rs.getString("日期").substring(0, 18);

				DateTime datetime = DateTime.parse(Date, format);
				Date = datetime.toString("yyyy-MM-dd");

				chugang = String.valueOf(rs.getInt("出港旅客"));
				kzl = rs.getFloat("客座率") + "%";
				jingang = String.valueOf(rs.getInt("进港旅客"));
				guozhan = String.valueOf(rs.getInt("过站旅客"));
				shangyong = String.valueOf(rs.getInt("商用座位"));
				yaoke = String.valueOf(rs.getInt("要客保障"));
				huowu = String.valueOf(rs.getInt("货物"));
				xingli = String.valueOf(rs.getInt("行李"));
				youjian = String.valueOf(rs.getInt("邮件"));
				teshu = rs.getString("特殊事件");

				jo.put("Date", Date);
				jo.put("chugang", chugang);
				jo.put("kzl", kzl);
				jo.put("jingang", jingang);
				jo.put("guozhan", guozhan);
				jo.put("shangyong", shangyong);
				jo.put("yaoke", yaoke);
				jo.put("huowu", huowu);
				jo.put("xingli", xingli);
				jo.put("youjian", youjian);
				jo.put("teshu", teshu);

				jsonData.add(jo);
				count++;
			}

			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		JSONObject jsonObject = new JSONObject();
		jsonObject.put("rows", jsonData);
		jsonObject.put("total", count);

		out.write(jsonObject.toString());
		out.flush();
		out.close();

		return null;
	}

	@RequestMapping("/getcolhistory")
	public String GetColHistory(String start, String end, HttpServletResponse response) throws IOException {
		System.out.println("start:" + start);
		System.out.println("end:" + end);

		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();

		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource2");

		JSONArray jsonData = new JSONArray();
		int count = 9;

		String startdate = start + " 00:00:00";
		String enddate = end + " 00:00:00";

		try {
			Connection conn = ds.getConnection();
			Statement st = conn.createStatement();

			StringBuffer sb = new StringBuffer(
					"SELECT sum(出港旅客) CG,sum(客座率)/COUNT(客座率) KZL,sum(进港旅客) JG,sum(过站旅客) GZ,sum(商用座位) SY, sum(要客保障) YK ,sum(货物) HW,sum(行李) XL,sum(邮件) YJ FROM YUNSHU ")
							.append("WHERE 日期 >=TO_DATE('").append(startdate)
							.append("', 'yyyy-mm-dd hh24:mi:ss') and 日期 <= TO_DATE('").append(enddate)
							.append("', 'yyyy-mm-dd hh24:mi:ss')");

			// System.out.println(sb.toString());

			String chugang = "";
			String kzl = "";
			String jingang = "";
			String guozhan = "";
			String shangyong = "";
			String yaoke = "";
			String huowu = "";
			String xingli = "";
			String youjian = "";

			ResultSet rs = st.executeQuery(sb.toString());
			while (rs.next()) {
				chugang = String.valueOf(rs.getInt("CG")) + " 人次";
				kzl = rs.getInt("KZL") + "%";
				jingang = String.valueOf(rs.getInt("JG")) + " 人次";
				guozhan = String.valueOf(rs.getInt("GZ")) + " 人次";
				shangyong = String.valueOf(rs.getInt("SY")) + " 座次";
				yaoke = String.valueOf(rs.getInt("YK")) + " 批次";
				huowu = String.valueOf(rs.getInt("HW")) + " 公斤";
				xingli = String.valueOf(rs.getInt("XL")) + " 公斤";
				youjian = String.valueOf(rs.getInt("YJ")) + " 公斤";

			}
			rs.close();

			JSONObject jo1 = new JSONObject();
			jo1.put("name", "出港旅客");
			jo1.put("data", chugang);
			jsonData.add(jo1);

			JSONObject jo2 = new JSONObject();
			jo2.put("name", "进港旅客");
			jo2.put("data", jingang);
			jsonData.add(jo2);

			JSONObject jo3 = new JSONObject();
			jo3.put("name", "过站旅客");
			jo3.put("data", guozhan);
			jsonData.add(jo3);

			JSONObject jo4 = new JSONObject();
			jo4.put("name", "商用座位");
			jo4.put("data", shangyong);
			jsonData.add(jo4);

			JSONObject jo5 = new JSONObject();
			jo5.put("name", "要客保障");
			jo5.put("data", yaoke);
			jsonData.add(jo5);

			JSONObject jo6 = new JSONObject();
			jo6.put("name", "出港平均客座率");
			jo6.put("data", kzl);
			jsonData.add(jo6);

			JSONObject jo7 = new JSONObject();
			jo7.put("name", "货物总量");
			jo7.put("data", huowu);
			jsonData.add(jo7);

			JSONObject jo8 = new JSONObject();
			jo8.put("name", "行李总量");
			jo8.put("data", xingli);
			jsonData.add(jo8);

			JSONObject jo9 = new JSONObject();
			jo9.put("name", "邮件总量");
			jo9.put("data", youjian);
			jsonData.add(jo9);

			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		JSONObject jsonObject = new JSONObject();
		jsonObject.put("rows", jsonData);
		jsonObject.put("total", count);

		out.write(jsonObject.toString());
		out.flush();
		out.close();

		return null;
	}

	@RequestMapping("/getrotahistory")
	public String GetRotaHistory(String month, HttpServletResponse response) throws IOException {
		System.out.println("start:" + month);

		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();

		String monthdate = month.replaceAll("-", "");

		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource");

		StringBuffer sb = new StringBuffer(
				"select rota_date,person_dept,rota_person,real_person,reason from jan_rota_contrast where rota_date like '")
						.append(monthdate)
						.append("%' and person_dept!='1号值班领导' and person_dept!='2号总值班长' and person_dept!='公安分局' and is_accordant=false ")
						.append("order by position(substring(person_dept from 1 for 2 ) in '运行安质公安安保机务商务地服物流急救飞行航站信息动力工程汽运停车实业凯亚'),rota_date");
		System.out.println(sb.toString());

		JSONArray jsonData = new JSONArray();
		int count = 0;

		try {
			Connection conn = ds.getConnection();
			Statement st = conn.createStatement();

			String date = "";
			String rota_person = "";
			String real_person = "";
			String dept = "";
			String reason = "";

			ResultSet rs = st.executeQuery(sb.toString());
			JSONObject jo = null;
			while (rs.next()) {
				jo = new JSONObject();
				date = rs.getString("rota_date");
				rota_person = rs.getString("rota_person");
				real_person = rs.getString("real_person");
				dept = rs.getString("person_dept");
				reason = rs.getString("reason");

				jo.put("date", date);
				jo.put("rota_person", rota_person);
				jo.put("real_person", real_person);
				jo.put("dept", dept);
				jo.put("reason", reason);

				jsonData.add(jo);
				count++;
			}

			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		JSONObject jsonObject = new JSONObject();
		jsonObject.put("rows", jsonData);
		jsonObject.put("total", count);

		out.write(jsonObject.toString());
		out.flush();
		out.close();
		return null;
	}
}